package mis.ds.ps.controller;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import mis.ds.ps.model.Mapper;
import mis.ds.ps.model.MapperComposite;
import mis.ds.ps.model.MapperLeaf;
import mis.ds.ps.model.Preferences;
import mis.ds.ps.model.iface.Entities;
import mis.ds.ps.model.obj.Docentes;
import mis.ds.ps.model.obj.Mappercomposto;
import mis.ds.ps.model.obj.Modulos;
import mis.ds.ps.model.obj.Versoes;
import mis.ds.ps.util.Config;
import mis.ds.ps.util.MysqlConnection;

import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;
import org.apache.poi.ss.usermodel.Sheet;

import com.mysql.jdbc.Connection;

public class Session {

	private static final String CONFIG_FILE = "tt-config.xml";

	private static Logger logger = Logger.getLogger(Session.class);
	private String name;
	private Config config;
	private Connection mysqlConn;
	private String fileToImport, sheetName;
	private Map<String, Mapper> mappersInUse;
	private MapperComposite compMapperInUse;
	private List<String> sheetNameList;
	private Preferences pref;

	private DataController dc;

	public Session(String name) {

		PropertyConfigurator.configure("log4j.properties");

		this.name = name;

		// Try read system configuration
		config = new Config(CONFIG_FILE);
		logger.debug("Configuration file [" + CONFIG_FILE + "] load with success");

		// Try attempt establish new connection
		mysqlConn = MysqlConnection.getMysqlConn(config);
		logger.debug("MySQL connection created with success");

		mappersInUse = new LinkedHashMap<String, Mapper>();
		dc = new DataController(mysqlConn);
	}

	public List<Versoes> getVersoesOnSession() {
		return dc.getVersoesOnDB();
	}

	public List<Mappercomposto> getMapCompOnSession() {
		return dc.getMapCompOnDB();
	}

	public void simulate() {

		MapperExecutor me = new MapperExecutor(fileToImport);

		// Take all mappers in use and read all

		for (Iterator<String> it = mappersInUse.keySet().iterator(); it.hasNext();) {
			me.setMapper(mappersInUse.get(it.next()));
			dc.commitSimulation(me.read());
		}

	}

	public void close() {
		try {
			mysqlConn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void setMysqlConn(Connection mysqlConn) {
		this.mysqlConn = mysqlConn;
	}

	public Connection getMysqlConn() {
		return mysqlConn;
	}

	public void setMappersInUse(Map<String, Mapper> mappersInUse) {
		this.mappersInUse = mappersInUse;
	}

	public Map<String, Mapper> getMappersInUse() {
		return mappersInUse;
	}

	public void addMapperInUse(String key, Mapper mapper) {
		mappersInUse.put(key, mapper);
	}

	public boolean setFileToImport(String fileToImport) {

		String extension = fileToImport.substring((fileToImport.length() - 4), fileToImport.length());
		if (fileToImport != null && (extension.equalsIgnoreCase(".xls") || extension.equalsIgnoreCase("xlsx"))) {
			this.fileToImport = fileToImport;
			logger.debug("Import File to be used on execution is [" + fileToImport + "]");
			return true;
		} else {
			logger.debug("Import File not valid [" + fileToImport + "]");
			return false;
		}

	}

	public String getFileToImport() {
		return fileToImport;
	}

	public List<String> getFileSheets() {

		sheetNameList = new LinkedList<String>();
		MapperExecutor me = new MapperExecutor(fileToImport);
		List<Sheet> sheetList = me.getFileSheets();

		for (Iterator<Sheet> it = sheetList.iterator(); it.hasNext();) {
			sheetNameList.add(it.next().getSheetName());
		}

		return sheetNameList;
	}

	public Result testSimulate() {

		MapperLeaf mp = new MapperLeaf("new");
		mp.setEntity(Entities.DOCENTES);
		mp.setAttribute("nome");
		mp.setSheetName(sheetNameList.get(3));
		// mp.setSheet(3);
		mp.setStartCell(1, 0);
		mp.setEndCell(66, 0);
		logger.debug("MapperLeaf in use [" + mp + "]");

		MapperExecutor me = new MapperExecutor(fileToImport, mp);

		return me.read();

	}
	
	public void testSimulateModule2() {
		pref = new Preferences(fileToImport);
		pref.Read();
	}

	public void setSheetName(String sheetName) {
		this.sheetName = sheetName;
		logger.debug("Sheet in use [" + sheetName + "]");
	}

	public String getSheetName() {
		return sheetName;
	}

	public boolean createCompMapper(String name) {

		compMapperInUse = new MapperComposite(name);

		Mappercomposto dbCompMapper = new Mappercomposto();
		dbCompMapper.setNome(name);

		return dc.insertMapperComposto(dbCompMapper);
	}

	public void setCompMapperInUse(MapperComposite compMapperInUse) {
		this.compMapperInUse = compMapperInUse;
	}

	public MapperComposite getCompMapperInUse(String name) {

		String realName = name.substring(name.indexOf("-") + 1, name.length());
		int mapperID = dc.getMapperIdByName(realName);

		logger.debug("Getting index " + mapperID + " for CompMapperName " + name);

		// creation if actual mapper
		if (compMapperInUse == null) {
			compMapperInUse = new MapperComposite(realName);
		}
		compMapperInUse.setName(realName);

		// Get single mappers with id retrieved
		List<MapperLeaf> mappersById = new LinkedList<MapperLeaf>();

		for (Iterator<mis.ds.ps.model.obj.Mapper> it = dc.getMappersByCompId(mapperID).iterator(); it.hasNext();) {

			logger.debug("Creating a controller version of Mapper ");

			mis.ds.ps.model.obj.Mapper dbMapper = it.next();

			MapperLeaf ml = new MapperLeaf(dbMapper.getNome());

			String ent = dbMapper.getEntity();
			if (ent.equalsIgnoreCase("DISCIPLINAS")) {
				ml.setEntity(Entities.DISCIPLINAS);
			} else if (ent.equalsIgnoreCase("DOCENTES")) {
				ml.setEntity(Entities.DOCENTES);
			} else if (ent.equalsIgnoreCase("CURSOS")) {
				ml.setEntity(Entities.CURSOS);
			} else if (ent.equalsIgnoreCase("SALAS")) {
				ml.setEntity(Entities.SALAS);
			} else if (ent.equalsIgnoreCase("MODULOS")) {
				ml.setEntity(Entities.MODULOS);
			} else if (ent.equalsIgnoreCase("TURMAS")) {
				ml.setEntity(Entities.TURMAS);
			}

			ml.setAttribute(dbMapper.getAttribute());
			ml.setSheet(dbMapper.getSheetindex());

			String mapValue = dbMapper.getMapvalue();
			String[] values = mapValue.split(";");

			String REGEX = "[a-zA-Z]";
			Pattern p = Pattern.compile(REGEX);

			// start cell
			String startCell = values[0];
			ml.setDbStartCell(startCell);
			Matcher m = p.matcher(startCell); // get a matcher object

			int count = 0;
			while (m.find()) {
				count++;
			}
			String startRow = startCell.substring(0, count);

			if (startRow.length() == 1) {
				ml.setStartCell(((int) startRow.charAt(0) - 64), Integer.valueOf(startCell.substring(1, startCell.length())));
			}

			// end cell
			String endCell = values[1];
			ml.setDbEndCell(endCell);
			Matcher m2 = p.matcher(endCell); // get a matcher object

			int count2 = 0;
			while (m.find()) {
				count2++;
			}
			String startRow2 = endCell.substring(0, count);

			if (startRow2.length() == 1) {
				ml.setEndCell(((int) startRow2.charAt(0) - 64), Integer.valueOf(endCell.substring(1, endCell.length())));
			}

			compMapperInUse.addMapper(ml);
			logger.debug("Adding mapper to use " + ml);
		}

		return compMapperInUse;
	}
	
	public List<Modulos> getModulosOnSession() {		
		return dc.getModulosOnDB();
	}
	
	public List<Docentes> getDocentesOnSession() {		
		return dc.getDocentesOnDB();
	}
}
